﻿<%@ WebHandler Language="C#" Class="ContainerShipRecordDashBoardRB" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;
using System.Collections;

/**
 * Description: 无损|热表转工看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.8.26
 */

public class ContainerShipRecordDashBoardRB : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["action"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string action = jObject["action"].ToString();
                switch (action)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    Result getData(JObject jObject)
    {
        string sql = @"
SELECT t.productname,t.togetherflag,t.specname,t.Reciver,t.submitePerson,t.factoryname,t.submitdate,t.plandate,
       t.estimatedcompletedate,t.status,SUM(t.qty) qty,t.isship
FROM ( 
     SELECT cci.productname,cci.togetherflag,cci.containername,ws.workflowstepname specname,cci.qty,e.fullname Reciver,
            e2.fullname submitePerson,f.factoryname,to_char(cr.senddate,'yyyy-mm-dd') submitdate,
            cr.needcompletedate plandate,cr.estimatedcompletedate,to_char(csr.recivedate,'yyyy-mm-dd') returndate,cr.ToAcceptDate,csr.CompleteDate,
            CASE WHEN pdr.parentid IS NOT NULL THEN '已拒收'
                 WHEN pdr.parentid IS NULL AND csr.recivedate IS NOT NULL THEN '已转回' 
                 WHEN pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '已完成'
                 WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '已接收'
                 WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL  AND csr.recivedate IS NULL THEN '已转出' END status,cci.isship
     FROM containershiprecord cr
     LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
     LEFT JOIN ContainerShipRecive csr ON csr.containershiprecordid = cr.containershiprecordid
     LEFT JOIN workflowstep ws on ws.workflowstepid = cr.workflowstepid
     LEFT JOIN employee e ON e.employeeid = cr.ToAcceptor
     LEFT JOIN employee e2 ON e2.employeeid = cr.proposerid
     LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
     LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
     WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 AND cr.needcompletedate IS NOT NULL 
           AND ((csr.reciver IS NULL) OR (SYSDATE - csr.recivedate) < 1)
";
        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format("AND cr.tofactoryid = '{0}' ", jObject["factoryid"].ToString());
        }
        sql += ") t WHERE 1=1";
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            sql += string.Format("AND t.status = '{0}' ", jObject["status"].ToString());
        }
        sql += @"
 GROUP BY t.productname,t.togetherflag,t.specname,t.Reciver,t.submitePerson,t.factoryname,t.submitdate,t.plandate,t.estimatedcompletedate,t.status,t.isship
 ORDER BY t.productname,t.togetherflag,t.specname ";

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dtResult = dto.DBTable;

        dtResult.Columns.Add("returndate", typeof(string));
        dtResult.Columns.Add("ToAcceptDate", typeof(string));
        dtResult.Columns.Add("CompleteDate", typeof(string));
        dtResult.Columns.Add("delayDays", typeof(string));

        DataTable dtFilter = dtResult.DefaultView.ToTable(true, "togetherflag", "specname");

        StringBuilder sbFilter = new StringBuilder();
        sbFilter.Append("(");
        foreach (DataRow dr in dtFilter.Rows)
        {
            sbFilter.AppendFormat("(cci.togetherflag = '{0}' AND ws.workflowstepname = '{1}') OR ", dr["togetherflag"], dr["specname"]);
        }
        sbFilter.Remove(sbFilter.ToString().Length - 3, 3);
        sbFilter.Append(")");

        StringBuilder sb = new StringBuilder();
        sql = @"
SELECT cci.productname,cci.togetherflag,cci.containername,ws.workflowstepname specname,cci.qty,
       f.factoryname,to_char(cr.senddate,'yyyy-mm-dd') submitdate,
       cr.needcompletedate plandate,cr.estimatedcompletedate,to_char(csr.recivedate,'yyyy-mm-dd') returndate,
       to_char(cr.ToAcceptDate,'yyyy-mm-dd') ToAcceptDate,to_char(csr.CompleteDate,'yyyy-mm-dd') CompleteDate,
       CASE WHEN pdr.parentid IS NOT NULL THEN 0
            WHEN csr.CompleteDate IS NULL THEN TRUNC((SYSDATE - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2)
            ELSE TRUNC((csr.CompleteDate - to_date(to_char(cr.needcompletedate,'yyyy/mm/dd') || ' 23:59:59','yyyy/mm/dd hh24:mi:ss') ),2) END delayDays,
       CASE WHEN pdr.parentid IS NOT NULL THEN '已拒收'
            WHEN pdr.parentid IS NULL AND csr.recivedate IS NOT NULL THEN '已转回' 
            WHEN pdr.parentid IS NULL AND csr.CompleteDate IS NOT NULL AND csr.recivedate IS NULL THEN '已完成'
            WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NOT NULL AND csr.CompleteDate IS NULL AND csr.recivedate IS NULL THEN '已接收'
            WHEN pdr.parentid IS NULL AND cr.ToAcceptDate IS NULL AND cr.senddate IS NOT NULL  AND csr.recivedate IS NULL THEN '已转出' END status
FROM containershiprecord cr
LEFT JOIN containercurrentinfo cci ON cci.containerid = cr.containerid
LEFT JOIN ContainerShipRecive csr ON csr.containershiprecordid = cr.containershiprecordid
LEFT JOIN workflowstep ws on ws.workflowstepid = cr.workflowstepid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN processdealrecord pdr ON pdr.parentid = cr.containershiprecordid AND pdr.processstatus = 0 AND (pdr.processtype = 1 OR pdr.processtype = 4)
WHERE cr.isuse = 0 AND cci.isinstore = 0 AND cci.containerstatus <> 2 AND cr.needcompletedate IS NOT NULL
      AND ((csr.reciver IS NULL) OR (SYSDATE - csr.recivedate) < 1)
";

        if (jObject["factoryid"] != null && jObject["factoryid"].ToString() != "")
        {
            sql += string.Format("AND cr.tofactoryid = '{0}' ", jObject["factoryid"].ToString());
        }

        sql += " AND " + sbFilter.ToString();

        DataTable dtDetail = dal.GetDataTable(sql);

        foreach (DataRow dr in dtResult.Rows)
        {
            string filter = string.Format("togetherflag = '{0}' and specname = '{1}' and status = '{2}'", dr["togetherflag"], dr["specname"], dr["status"]);
            DataRow[] datarowList = dtDetail.Select(filter);

            if (datarowList.Length > 0)
            {
                dr["returndate"] = datarowList[0]["returndate"];
                dr["ToAcceptDate"] = datarowList[0]["ToAcceptDate"];
                dr["CompleteDate"] = datarowList[0]["CompleteDate"];
                dr["delayDays"] = datarowList[0]["delayDays"];
            }
            else
            {
                dr["returndate"] = "";
                dr["ToAcceptDate"] = "";
                dr["CompleteDate"] = "";
                dr["delayDays"] = "";
            }
        }
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dtResult;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}